﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_CIB004:DA_Matrix
    {
        public DataTable GetSummaryData(bool p_checkAccount,string p_yyyymm)
        {
            string sql, checkAccount;
            GetCriteriaString();
            checkAccount = GetExclusiveOfAccount(p_checkAccount);
            sql = string.Format(getSummaryStatement()
                    , CriteriaString
                    , checkAccount
                    , p_yyyymm
                );
            AddParameter(sql);
            return getData();         
        }

        public override string[] GetDistinctField()
        {
            return new string[]{
                             "YYYYMM"
                            ,"會計科目"                            
                            };
        }

        public override DataSet GetCriteria()
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(Utility.GetYYYYMMRange(Convert.ToDateTime("2009-09-01")));
            ds.Tables.Add(Utility.GetCIBAccountNoData());
            return ds;
        }

        protected string getSummaryStatement()
        {
            //{0} → YYYYMM            
            //{1} → 排除公庫
            //{2} → C_BO1_TD_TWD_V 要自己用一個月份
            return @"SELECT 
                        --A.LNAC_ID   	
                        dbo.ReplaceCIBValue(C.LNAC_NAME,1)LNAC_NAME                        
                        ,(SELECT RM_GROUP FROM RM R WHERE R.LNAC_ID = A.LNAC_ID) [客群]
                        ,CONVERT(NVARCHAR,CAST(A.TOT_BAL AS MONEY),1)AS 放款均額                        
                        ,A.放款利率
                        ,CONVERT(NVARCHAR,CAST(B.AVGBAL AS MONEY),1)AS 存款均額
                        ,B.存款利率 
                        FROM (SELECT 
			                        A.LNAC_ID
			                        ,A.YYYYMM	
			                        ,SUM(A.均額) AS TOT_BAL	 
			                        ,SUM(A.承作利率*A.均額)/SUM(A.均額) AS 放款利率
			                        --COUNT(*)
		                        FROM 企金 AS A WITH(INDEX(IDX_CIB_YM_CUR_ACNO))
		                        WHERE 
			                        A.均額>0 
                                    And A.cur In ('TWD') 
			                        {0}
			                        {1}
		                        GROUP BY A.LNAC_ID,A.YYYYMM
		                        )A INNER JOIN 
	                        (SELECT
			                        A.ID
			                        ,A.YYYYMM
			                        ,SUM(A.[Avg Bal]) AS AVGBAL
			                        ,SUM(A.[Int Rate]*A.[Avg Bal])/SUM(A.[Avg Bal]) AS 存款利率
		                        FROM C_BO1_TD_TWD_V AS A
		                        WHERE 1=1 --B.ID = '00995920'
			                       AND A.YYYYMM = '{2}'
		                        GROUP BY  A.ID,A.YYYYMM
		                        )B
                                ON A.LNAC_ID = B.ID AND A.YYYYMM = B.YYYYMM
                                LEFT OUTER JOIN (
						                                SELECT DISTINCT A.LNAC_ID,A.LNAC_NAME
						                                FROM 企金 AS A	 WITH(INDEX(IDX_CIB_YM_CUR_ACNO))
                                                        WHERE 
			                                                A.均額>0 
                                                            And A.cur In ('TWD') 
			                                                {0}
			                                                {1}	
		                                )C
		                                ON A.LNAC_ID = C.LNAC_ID



";
        }

        public DA_CIB004()
        {
            tableName = "企金";
        }

    }
}
